﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Copad.BusinessCard.DAL;
using Copad.BusinessCard.Model;
using System.Net;

namespace Copad.BusinessCard.BLL
{
    public class CardBLL : BLL<CardInfo>
    {
        static string SQL_SELECT_BY_CATEGORY_ID = "select * from cardinfo where {2} cardid in (select cardid from cardcategory where categoryid=(select categoryid from category where id={0})) and id not in(select cardid from CardUserPermissions where UserID={1}) and id not in (select  cardid from cardgrouppermissions where groupid in(select groupid from usergroup where UID={1}))  order by {3}";
        static string SQL_SELECT_UNGROUPED_BY_USER = "select * from cardinfo where {0} cardid not in(select distinct cardid from cardcategory)  and id not in(select cardid from CardUserPermissions where UserID={1}) and id not in (select  cardid from cardgrouppermissions where groupid in(select groupid from usergroup where UID={1}))  order by {2}";
        static string SQL_CONDITION = "(Name like '%{0}%' or CardID in (select CardID from CardRemark where Remark like '%{0}%') or CardID in (select CardID from CardCompany where Company like '%{0}%') or CardID in (select CardID from CardAddress where Address1 like '%{0}%' or Address2 like '%{0}%')) and ";
        static string SQL_SELECT_FAVORITE = "select * from cardinfo where {1} id in (select  itemid from dbo.Favorite where userid={0})  order by name";
        static string SQL_SELECT_ALL = "select * from cardinfo where {1} id not in(select cardid from CardUserPermissions where UserID={0}) and id not in (select  cardid from cardgrouppermissions where groupid in(select groupid from usergroup where UID={0})) order by {2}";
        static FavoriteBLL bll = new FavoriteBLL();

        public CardBLL()
        {
            OrderBy = "name";
        }

        public string OrderBy { get; set; }

        public List<CardInfo> GetAll(long uid, string searchKey = null)
        {
            string condition = "";
            if (!string.IsNullOrEmpty(searchKey))
            {
                condition = string.Format(SQL_CONDITION, searchKey);
            }
            string sql = string.Format(SQL_SELECT_ALL, uid, condition, OrderBy);
            return dataBase.Select<CardInfo>(sql);
        }

        /// <summary>
        /// 根据分组编号获取cardinfo
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CardInfo> GetCardInfoByCID(long id, long uid, string searchKey = null)
        {
            string condition = "";
            if (!string.IsNullOrEmpty(searchKey))
            {
                condition = string.Format(SQL_CONDITION, searchKey);
            }
            string sql = string.Format(SQL_SELECT_BY_CATEGORY_ID, id, uid, condition, OrderBy);
            return dataBase.Select<CardInfo>(sql);
        }

        /// <summary>
        /// 根据名片编号获取cardinfo
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CardInfo GetCardInfoByID(long id)
        {
            return dataBase.Select<CardInfo>(x => x.ID == id).FirstOrDefault();
        }

        /// <summary>
        /// 获取未分组的cardinfo
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CardInfo> GetUngroupedCardInfo(long uid, string searchKey = "")
        {
            string condition = "";
            if (!string.IsNullOrEmpty(searchKey))
            {
                condition = string.Format(SQL_CONDITION, searchKey);
            }
            string sql = string.Format(SQL_SELECT_UNGROUPED_BY_USER, condition, uid, OrderBy);
            return dataBase.Select<CardInfo>(sql);
        }

        public void Insert(Card card)
        {
            dataBase.BeginTransaction();
            dataBase.Insert(card.Summary);
            card.Address.ForEach(x => { dataBase.Insert(x); });
            card.Category.ForEach(x => { dataBase.Insert(x); });
            card.Company.ForEach(x => { dataBase.Insert(x); });
            card.Email.ForEach(x => { dataBase.Insert(x); });
            card.IM.ForEach(x => { dataBase.Insert(x); });
            card.Number.ForEach(x => { dataBase.Insert(x); });
            card.Remark.ForEach(x => { dataBase.Insert(x); });
            card.Website.ForEach(x => { dataBase.Insert(x); });
            dataBase.EndTransaction();
        }

        public void Update(Card card)
        {
            dataBase.BeginTransaction();
            dataBase.Update(card.Summary);
            dataBase.Delete<CardAddress>(x => x.CardID == card.Summary.CardID);
            card.Address.ForEach(x => { dataBase.Insert(x); });
            dataBase.Delete<CardCategory>(x => x.CardID == card.Summary.CardID);
            card.Category.ForEach(x => { dataBase.Insert(x); });
            dataBase.Delete<CardCompany>(x => x.CardID == card.Summary.CardID);
            card.Company.ForEach(x => { dataBase.Insert(x); });
            dataBase.Delete<CardEmail>(x => x.CardID == card.Summary.CardID);
            card.Email.ForEach(x => { dataBase.Insert(x); });
            dataBase.Delete<CardIM>(x => x.CardID == card.Summary.CardID);
            card.IM.ForEach(x => { dataBase.Insert(x); });
            dataBase.Delete<CardPhoneNumber>(x => x.CardID == card.Summary.CardID);
            card.Number.ForEach(x => { dataBase.Insert(x); });
            dataBase.Delete<CardRemark>(x => x.CardID == card.Summary.CardID);
            card.Remark.ForEach(x => { dataBase.Insert(x); });
            dataBase.Delete<CardWebsite>(x => x.CardID == card.Summary.CardID);
            card.Website.ForEach(x => { dataBase.Insert(x); });
            dataBase.EndTransaction();
        }

        public void Delete(string cardID)
        {
            dataBase.BeginTransaction();
            dataBase.Delete<CardInfo>(x => x.CardID == cardID);
            dataBase.Delete<CardAddress>(x => x.CardID == cardID);
            dataBase.Delete<CardCategory>(x => x.CardID == cardID);
            dataBase.Delete<CardCompany>(x => x.CardID == cardID);
            dataBase.Delete<CardEmail>(x => x.CardID == cardID);
            dataBase.Delete<CardIM>(x => x.CardID == cardID);
            dataBase.Delete<CardPhoneNumber>(x => x.CardID == cardID);
            dataBase.Delete<CardRemark>(x => x.CardID == cardID);
            dataBase.Delete<CardWebsite>(x => x.CardID == cardID);
            dataBase.EndTransaction();
        }

        public override void Delete(CardInfo card)
        {
            Delete(card.CardID);
        }

        /// <summary>
        /// 同步数据
        /// </summary>
        public OperationResult Sync(IList<Card> cards)
        {
            OperationResult result = new OperationResult { Successful = true, RelateObject = cards };
            int errorCount = 0;
            var existsCards = dataBase.Select<CardInfo>();
            var existsCardID = (from x in existsCards select x.CardID).ToList();
            var syncCardID = (from x in cards select x.Summary.CardID).ToList();
            existsCards.ForEach(x =>
            {
                if (!syncCardID.Contains(x.CardID))//不存在则删除
                {
                    try
                    {
                        Delete(x.CardID);
                        //var card = existsCards.Where(w => w.CardID == x.CardID).First();
                    }
                    catch (Exception ex)
                    {
                        result.AppendLine("{Operation:Delete,CardID:" + x.CardID + ",Exception:" + ex.Message + "}");
                        result.Successful = false;
                        errorCount++;
                    }
                }
            });

            cards.ToList().ForEach(x =>
            {
                try
                {
                    if (existsCardID.Contains(x.Summary.CardID))//修改
                    {
                        var existsCard = existsCards.Find(w => w.CardID == x.Summary.CardID);
                        x.Summary.ID = existsCard.ID;
                        if (existsCard.SyncID != x.Summary.SyncID || string.IsNullOrEmpty(x.Summary.SyncID))//同步编号不同则修改
                        {
                            Update(x);
                        }
                    }
                    else//新增
                    {
                        Insert(x);
                    }
                }
                catch (Exception ex)
                {
                    result.AppendLine("{Operation:Insert/Update,CardID:" + x.Summary.CardID + ",Exception:" + ex.Message + "}");
                    result.Successful = false;
                    errorCount++;
                }
            });

            result.RelateObject = errorCount;
            return result;
        }

        public Card Get(CardInfo cardInfo, long uid)
        {
            Card card = new Card();
            card.Summary = cardInfo;
            card.Address.AddRange(dataBase.Select<CardAddress>(x => x.CardID == cardInfo.CardID).OrderBy(x => x.Address1));
            card.Category.AddRange(dataBase.Select<CardCategory>(x => x.CardID == cardInfo.CardID));
            card.Company.AddRange(dataBase.Select<CardCompany>(x => x.CardID == cardInfo.CardID));
            card.Email.AddRange(dataBase.Select<CardEmail>(x => x.CardID == cardInfo.CardID));
            card.IM.AddRange(dataBase.Select<CardIM>(x => x.CardID == cardInfo.CardID));
            card.Number.AddRange(dataBase.Select<CardPhoneNumber>(x => x.CardID == cardInfo.CardID).OrderBy(x => x.Type));
            card.Remark.AddRange(dataBase.Select<CardRemark>(x => x.CardID == cardInfo.CardID));
            card.Website.AddRange(dataBase.Select<CardWebsite>(x => x.CardID == cardInfo.CardID));
            card.FavoriteInfo = dataBase.Select<Favorite>(w => w.Type == FavoriteTypeEnum.Card && w.ItemID == cardInfo.ID && w.UserID == uid).FirstOrDefault();
            return card;
        }

        public Card Get(long id, long uid)
        {
            CardInfo cardInfo = GetCardInfoByID(id);
            if (cardInfo == null) { return null; }
            return Get(cardInfo, uid);
        }

        public OperationResult AddFavorite(Favorite favorite)
        {
            OperationResult result = new OperationResult();
            var item = bll.Get(x => x.UserID == favorite.UserID && x.ItemID == favorite.ItemID && x.Type == favorite.Type).FirstOrDefault();
            if (item != null)
            {
                result.Successful = false;
            }
            else
            {
                bll.Insert(favorite);
            }
            return result;
        }

        public void RemoveFavorite(long fid)
        {
            bll.Delete(new Favorite { ID = fid });
        }

        public List<CardInfo> GetFavoriteCard(long uid, string searchKey = null)
        {
            string condition = "";
            if (!string.IsNullOrEmpty(searchKey))
            {
                condition = string.Format(SQL_CONDITION, searchKey);
            }
            string sql = string.Format(SQL_SELECT_FAVORITE, uid, condition);
            return dataBase.Select<CardInfo>(sql);
        }


    }
}
